﻿namespace MonetDbDataProviderTest
{
    using System;
    using System.Collections.Generic;

    using MonetDbDataProvider;

    using NUnit.Framework;

    [TestFixture]
    public class MonetDbCommandTest : BaseTest
    {
        [SetUp]
        public void Setup()
        {
            //var sql = 
            //    "CREATE TABLE test.users (firstname varchar(200), lastname varchar(200), createdate date);";

            var sql = "INSERT INTO test.users (firstname, lastname, createdate) VALUES ('Ted', 'Hills','2013-01-01'), ('Jim', 'Hills','2013-05-01'), ('John', 'Hills','2013-08-01'), ('Shaylee', 'Hills','2013-09-01')";

            base.RunSQL(sql);
        }

        [TearDown]
        public void Teardown()
        {
            //var sql = "DROP TABLE test.TestTable;";
            var sql = "DELETE FROM test.users;";
            base.RunSQL(sql);
        }

        [Test]
        public void SimpleQueryTest()
        {
            var users = new List<string>();

            using (var cmd = new MonetDbCommand(base.MonetDbConn))
            {
                cmd.Connection.Open();
                cmd.CommandText = "SELECT * FROM  test.users;";
                var reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    users.Add(reader["firstname"].ToString());
                }

                cmd.Connection.Close();
            }

            Assert.AreEqual(4, users.Count);
        }

        [Test]
        public void SimpleUpdateQueryWithoutParamsTest()
        {
            var currentDate = DateTime.Now;
            int result;

            using (var cmd = new MonetDbCommand(base.MonetDbConn))
            {
                cmd.Connection.Open();
                cmd.CommandText = "UPDATE test.users SET createdate='" + currentDate.ToString("yyyy-MM-dd") + "' WHERE firstname='Ted';";
                result = cmd.ExecuteNonQuery();

                cmd.Connection.Close();
            }

            Assert.AreEqual(-1, result);
        }

        [Test]
        public void UpdateQueryWithTextParamsTest()
        {
            int result;

            using (var cmd = new MonetDbCommand(base.MonetDbConn))
            {
                cmd.Connection.Open();
                var firstnameParam = cmd.CreateParameter();
                firstnameParam.ParameterName = "@firstname";
                firstnameParam.Value = "Shaylee";
                cmd.Parameters.Add(firstnameParam);

                var lastnameParam = cmd.CreateParameter();
                lastnameParam.ParameterName = "@lastname";
                lastnameParam.Value = "Hills";
                cmd.Parameters.Add(lastnameParam);

                cmd.CommandText = "UPDATE test.users SET lastname=@lastname WHERE firstname=@firstname;";
                result = cmd.ExecuteNonQuery();

                cmd.Connection.Close();
            }

            Assert.AreEqual(-1, result);
        }

        [Test]
        public void DeleteQueryWithParamTest()
        {
            int result;

            using (var cmd = new MonetDbCommand(base.MonetDbConn))
            {
                cmd.Connection.Open();
                var firstnameParam = cmd.CreateParameter();
                firstnameParam.ParameterName = "@firstname";
                firstnameParam.Value = "Shaylee";
                cmd.Parameters.Add(firstnameParam);

                cmd.CommandText = "DELETE FROM test.users WHERE firstname=@firstname;";
                result = cmd.ExecuteNonQuery();

                // Check
                Assert.AreEqual(-1, result);

                // Now double check with a SELECT
                cmd.CommandText = "SELECT * FROM test.users WHERE firstname=@firstname;";

                var reader = cmd.ExecuteReader();

                try
                {
                    Assert.IsFalse(reader.Read());
                }
                catch (Exception ex)
                {
                    Assert.Fail(ex.Message);
                }
                finally
                {
                    cmd.Connection.Close();
                }
            }
        }

        [Test]
        public void BatchSelectResultsTest()
        {
            using (var cmd = new MonetDbCommand(base.MonetDbConn))
            {
                // Arrange
                cmd.Connection.Open();
                cmd.CommandText = "SELECT * FROM test.users ORDER BY firstname ASC;"
                                + "SELECT * FROM test.users ORDER BY firstname DESC;";

                // Act
                var reader = cmd.ExecuteReader();

                // Assert first result
                Assert.IsTrue(reader.Read());
                Assert.AreEqual("Jim", reader["firstname"].ToString());

                // Act
                reader.NextResult();

                // Assert second result
                Assert.IsTrue(reader.Read());
                Assert.AreEqual("Ted", reader["firstname"].ToString());

                cmd.Connection.Close();
            }
        }

        [Test]
        public void BatchSelectAndUpdateResultsTest()
        {
            using (var cmd = new MonetDbCommand(base.MonetDbConn))
            {
                // Arrange
                cmd.Connection.Open();
                cmd.CommandText = "SELECT * FROM test.users ORDER BY firstname ASC;"
                                + "UPDATE test.users SET firstname='Teddy' WHERE firstname='Ted';"
                                + "SELECT * FROM test.users WHERE firstname='Teddy';";

                // Act
                var reader = cmd.ExecuteReader();

                // Assert first result
                Assert.IsTrue(reader.Read());
                Assert.AreEqual("Jim", reader["firstname"].ToString());

                // Act
                reader.NextResult();

                // Assert second result
                Assert.IsFalse(reader.Read());

                // Act
                reader.NextResult();

                // Assert third result
                Assert.IsTrue(reader.Read());
                Assert.AreEqual("Teddy", reader["firstname"].ToString());

                cmd.Connection.Close();
            }
        }
    }
}
